/**
* Class to manage DB connection and transactions
*
* Project: idbCrownInServer
* Author:  TM
* Date:    01/03/2013
*
* History
* ------------------
* 01/03/2013 - TM - Initial version.
*/
 
package DataModel;

import java.sql.*;
import java.util.*;
import java.text.*;

import Application.*;
import Util.*;

public class libDatabaseConnection
{
  /** Database main connection singleton variable */
  public static libDatabaseConnection connection;
  /** Default timezone for date recording */
  public static TimeZone defaultDbRecordTZ = TimeZone.getTimeZone("UTC");
  /** Application support */
  protected static libApplication appSupport;
  /** Connections properties  */
  protected libDatabaseProperties properties;
  /** Connection name */
  protected String name;
  /** Database native connection - null if not yet connected */
  protected Connection dbConnection;
  /** Datamodels registered in connection */
  protected Vector dataModelList = new Vector(0,10);
  /** DB transaction in progress */
  protected boolean transaction = false;
  /** Datamodels involved in DB transaction - to be able to reload them when DB rolls back */
  protected Vector dataModelTransactionList;
  /** DB transaction semaphore - guarantess single active transaction/connection */
  protected libSemaphore transactionSemaphore = new libSemaphore();
  /** Flag to check rollback action in nested transactions
      0=OK to commit 1=Rollback action has occurred: rollback at DB transaction level */
  protected int rollBackFlag = 0;

  //
  // Connection singleton support
  //
  /** Create main database connection singleton */
  public static synchronized void createDBConnection()
  {
    appSupport = libApplication.getInstance();    
    connection = new libDatabaseConnection();
  }

  /** Destroy database connection singleton */
  public static synchronized void destroy()
  {
    connection = null;
  }

  /** Main database connection (singleton) */
  public static synchronized libDatabaseConnection getMainConnection()
  {
    if (connection == null)
      createDBConnection();
    return connection;
  }
  
  /** Row-lock SELECT statement (from standard SELECT) for the default DB connection */
  public static String getSQLLockRow(String sql)
  {
    return connection != null ? connection.buildSqlLockRow(sql) : sql;
  }

  //
  // Connection instance
  //
  /** Constructor */
  public libDatabaseConnection(String name, String sqlType)
  {
    this.name = name;
    properties = new libDatabaseProperties(sqlType, defaultDbRecordTZ);
  }

  /** Constructor */
  public libDatabaseConnection(String sqlType)
  {
    this(null, sqlType);
  }

  /** Construye el objeto pero no inicializa la conexion - public to allow extra connections */
  public libDatabaseConnection()
  {
    this(null);
  }

  /** Antes de destruir el objeto se cierra la conexion.*/
  protected void finalize() throws Throwable
  {
    if (isConnected())
      disconnect();

    super.finalize();
  }

  /** Connection name */
  public String getName()
  {
    return name;
  }
  
  /** JDBC connection object */
  public Connection getHardConnection()
  {
    return dbConnection;
  }

  //
  // Database connection support
  //
  /** Establish default connection */
  public void connect() throws ClassNotFoundException, SQLException
  {
    if (properties.getDbType() == properties.NO_DB)
     return;
     
    String databaseURL  = getDbURL();
    String driverName   = getDbDriver();
    String userName     = getDbUser();
    String userPassword = getDbUserPassword();

    connect(databaseURL, driverName, userName, userPassword);
  }

  /** Establish general Connection */
  public void connect(String databaseURL, String driverName, String userName, String userPassword)
              throws ClassNotFoundException, SQLException
  {
    //Checks
    if (isConnected())
    {
       appSupport.showErrorMsg(libExceptionConst.IS_CONNECTED);
       return;
    }

    if (driverName == null || driverName.equals(""))
    {
       appSupport.showErrorMsg(libExceptionConst.EMPTY_DRIVE_NAME);
       return;
    }

    if (databaseURL == null || databaseURL.equals(""))
    {
       appSupport.showErrorMsg(libExceptionConst.EMPTY_URL);
       return;
    }
   
    debug(".driver: " + driverName );
    debug(".URL   : " + databaseURL);

    Class.forName(driverName);
    dbConnection = DriverManager.getConnection(databaseURL, userName, userPassword);

    DatabaseMetaData md = dbConnection.getMetaData();
    debug(".driver: " + md.getDriverName()
                + "|" + md.getDriverVersion());
    debug(".DB    : " + md.getDatabaseProductName()
                + "|" + md.getDatabaseProductVersion());
  }

  /** Close connection */
  public void disconnect() throws SQLException
  {
    if (properties.getDbType() == properties.NO_DB)
     return;

    //Checks
    if (isConnected())
    {
       appSupport.showErrorMsg(libExceptionConst.CANNOT_DISCONNECT);
       return;
    }

    dbConnection.close();
    dbConnection = null;
  }

  /** Connection established */
  public boolean isConnected()
  {
    return dbConnection != null;
  }

  /** Connection established and well */
  public boolean isAlive()
  {
    boolean ok = dbConnection != null;
    if (ok)
    {
      ResultSet rs = null;
      try                       // Try to get the table names and ignore the result.
      {
        DatabaseMetaData metaData = dbConnection.getMetaData();

        String[] types = { "TABLE" };  // We only try to get the user table names

        rs = metaData.getTables("",      // Catalog - retrieve tables without catalog
                                "",      // Schema  - retrieve tables without schema
                                "%",     // Tables  - matching any name
                                types );
      }
      catch (SQLException e)   // Exception means database is not alive
      {
        ok = false;
      }
      finally
      {
        if (rs != null)
          try
          {
            rs.close();
          }
          catch (Exception e) {}
      }
    }
    return ok;
  }

  /** Get the URL of the database  - depending on TYPE_DATABASE_SERVER
      Old statement connection (AeBRS 5.0.0):
      1 = Oracle: DB_URL+DB_URL_SERVER+":"+DB_URL_ORACLE_PORT+":"+DB_URL_ORACLE_SID
      2 = MSSQL : DB_URL+DB_URL_SERVER+"?database="+DB_URL_MSSQL_DATABASE
      New statement connection (since AeBRS 5.0.1)
  */
  public String getDbURL()
  {
    int    dbType   = properties.getDbType();
    String dbURL    = appSupport.getConfigValue("DB_URL");
    String dbServer = appSupport.getConfigValue("DB_URL_SERVER");
    String dbName   = appSupport.getConfigValue("DB_NAME");
    String dbPort   = appSupport.getConfigValue("DB_PORT");

    // IMPORTANT:
    // This 'if' has been implemented to keep consistency with
    // AeBRS 5.0.0 where "DB_NAME" and "DB_PORT" was not defined.
    // Ready to remove for greater versions than AeBRS 5.0.1
    if (dbName == null || dbPort == null)
    {
      if      (dbType == properties.ORACLE)
      {
        dbName = appSupport.getConfigValue("DB_URL_ORACLE_SID");
        dbPort = appSupport.getConfigValue("DB_URL_ORACLE_PORT");
      }
      else if (dbType == properties.MSSQL)
      {
        dbName = appSupport.getConfigValue("DB_URL_MSSQL_DATABASE");
        dbPort = "";
      }
    }
    Object[] arguments = {dbServer, dbName, dbPort};

    return MessageFormat.format(dbURL, arguments);
  }

  /** JDBC driver name (class name) */
  public String getDbDriver()
  {
    return appSupport.getConfigValue("DB_DRIVER");
  }

  /** Database user name */
  public String getDbUser()
  {
    return appSupport.getConfigValue("DB_USER");
  }

  /** Database user password */
  public String getDbUserPassword()
  {
    return appSupport.getConfigValue("DB_USER_PASSWORD");
  }

  //
  // Statement support
  //
  /** Create new statement */
  public Statement createStatement() throws SQLException
  {
    return createStatement(null);
  }

  /** Create new statement prepared for given SQL query */
  public Statement createStatement(String sql) throws SQLException
  {
    return sql != null ? dbConnection.prepareStatement(sql)
                       : dbConnection.createStatement();
  }

  /** Create new statement prepared for given SQL query */
  public Statement createStatement(String sql, int type) throws SQLException
  {
    return sql != null ? dbConnection.prepareStatement(sql, type)
                       : null;
  }

  /** Close statement */
  public void closeStatement(Statement statement) throws SQLException
  {
    if (statement != null)
      statement.close();
  }

  //
  // Connection exclusive access support
  //
  /** Semaphore to control exclusive access to connection */
  protected libSemaphore exclusiveAccess = new libSemaphore();
  
  /** Start exclusive access to connection */
  public void startExclusiveAccess() throws SQLException
  {
    try
    {
      exclusiveAccess.up();
    }
    catch (Exception e)
    {
      appSupport.showErrorMsg(e);
      throw new SQLException(e.toString());
    }
  }
  
  /** End exclusive access to connection */
  public void endExclusiveAccess()
  {
    exclusiveAccess.down();
  }
  
  //
  // Connection properties related methods
  //
  /** Database type - determines how record-lock query is built (see buildSqlLockRow())
                    & main connection JDBC URL -
      return value according to enumeration on libDatabaseProperties (JDBC_FOR_UPDATE=0|ORACLE=1|MMSQL=2)
    */
  public int getDbType()
  {
    return properties.getDbType();
  }

  /** Gets the SELECT statement to lock a row in the server from standard SELECT */
  public String buildSqlLockRow(String sql)
  {
    return properties.buildSqlLockRow(sql);
  }

  /** Sorting collator sequence */
  public RuleBasedCollator getCurrentRuleCollator()
  {
    return properties.getCurrentRuleCollator();
  }

  /** DB (hard) sorting is compatible with Java sorting rules */
  public boolean isCanHardSort()
  {
    return properties.isCanHardSort();
  }

  /** String maximum length */
  public Integer getMaxLengthChar()
  {
    return properties.getMaxLengthChar();
  }

  /** String concatenation operator */
  public String getDbStrConcat()
  {
    return properties.getDbStrConcat();
  }

  /** Function prefix for non-string data type object to string conversion */
  public String getToCharFuncBegin()
  {
    return properties.getToCharFuncBegin();
  }

  /** Function prefix for string data type object to integer number conversion */
  public String getToIntFuncBegin()
  {
    return properties.getToIntFuncBegin();
  }
 
  /** Maximum allowed for decimal number in database */
  public Double getDbMaxDouble()
  {
    return properties.getDbMaxDouble();
  }

  /** Minimum allowed for decimal number in database */
  public Double getDbMinDouble()
  {
    return properties.getDbMinDouble();
  }

  /** Number of digital figures decimal numbers should be rounded to - see lib.Util.libNormalizeNumber */
  public Integer getDbMaxSignificant()
  {
    return properties.getDbMaxSignificant();
  }

  /** Number of non-significant last decimal figures for rounding - see lib.Util.libNormalizeNumber */
  public Integer getDbSignifMargin()
  {
    return properties.getDbSignifMargin();
  }

  /** Boolean YES SQL string representation in the connection */
  public Number getDbTrueConst()
  {
    return properties.getDbTrueConst();
  }

  /** Boolean NO SQL string representation in the connection */
  public Number getDbFalseConst()
  {
    return properties.getDbFalseConst();
  }

  /** Timestamp SQL string representation in the connection*/
  public String getDbSQLDate(Timestamp obj)
  {
    return properties.getDbSQLDate(obj);
  }

  /** Time zone for recording dates into DB */
  public TimeZone getDbRecordTZ()
  {
    return properties.getDbRecordTZ();
  }

  /** Format for recording dates into DB */
  public DateFormat getDbDateFormat()
  {
    return properties.getDbDateFormat();
  }

  //
  // Debug utilities
  //

  /** Debug transaction utility method */
  protected void debugTransaction(String msg)
  {
    debug(" - " + Thread.currentThread().getName() + " TRANSACTION " + msg);
  }
  
  /** Get string for debug (including a message) */
  public String getDebugString(String msg)
  {
    StringBuffer buf = new StringBuffer("libDatabaseConnection");
    if (name != null)
      buf.append('(').append(name).append(')');
    return buf.append(msg).toString();
  }
  
  /** Get connection prefix string (name followed by dot separator) */
  public String getConnectionPrefixString()
  {
    return name != null ? (name + ".") : "";
  }
  
  /** Debug utility method */
  protected void debug(String msg)
  {
    libFileDebug.getInstance().write("libDatabaseConnection." + msg);
  }
  
}
